﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="DataSourcesHelper.cs" company="">
// </copyright>
// <summary>
//   The data sources helper.
// </summary>
// --------------------------------------------------------------------------------------------------------------------
namespace UITest.Helpers
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Runtime.InteropServices;
    using System.Xml;

    using Microsoft.Office.Interop.Excel;

    using DataTable = System.Data.DataTable;

    /// <summary>
    ///     The data sources helper.
    /// </summary>
    public class DataSourcesHelper
    {
        #region Public Methods and Operators

        /// <summary>
        /// The get database data.
        /// </summary>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <param name="query">
        /// The query.
        /// </param>
        /// <returns>
        /// The <see cref="System.Data.DataTable"/>.
        /// </returns>
        public DataTable GetDatabaseData(string connectionString, string query)
        {
            var sqlConnection = new SqlConnection(connectionString);
            sqlConnection.Open();
            var sqlCommand = new SqlCommand(query, sqlConnection);
            var dataTable = new DataTable();

            using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
            {
                sqlDataAdapter.Fill(dataTable);
            }

            sqlConnection.Close();
            sqlConnection.Dispose();
            return dataTable;
        }

        /// <summary>
        /// The get database data.
        /// </summary>
        /// <param name="sqlCommand">
        /// The sql command.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable"/>.
        /// </returns>
        public DataTable GetDatabaseData(SqlCommand sqlCommand)
        {
            var dataTable = new DataTable();
            sqlCommand.Connection.Open();

            using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
            {
                sqlDataAdapter.Fill(dataTable);
            }

            sqlCommand.Connection.Close();
            sqlCommand.Connection.Dispose();
            return dataTable;
        }

        /// <summary>
        /// The get excel data.
        /// </summary>
        /// <param name="excelFilePath">
        /// The excel file path.
        /// </param>
        /// <param name="worksheetName">
        /// The worksheet name.
        /// </param>
        /// <returns>
        /// The <see cref="System.Data.DataTable"/>.
        /// </returns>
        public DataTable GetExcelData(string excelFilePath, string worksheetName)
        {
            var excelApp = new Application();
            Sheets excelSheets;
            Workbook excelWorkwbook = null;
            var dataTable = new DataTable();
            try
            {
                excelWorkwbook = excelApp.Workbooks.Open(excelFilePath);
                excelSheets = excelWorkwbook.Worksheets;
                var excelWorksheet = (Worksheet)excelSheets.Item[worksheetName];

                if (excelWorksheet == null)
                {
                    return dataTable;
                }

                string cellContent;
                int rowCount = excelWorksheet.UsedRange.Rows.Count;
                int colCount = excelWorksheet.UsedRange.Columns.Count;
                Range range;

                DataColumn dataColumn;
                int ColumnID = 1;
                range = (Range)excelWorksheet.Cells[1, 1];
                while (!range.Text.ToString().Trim().Equals(string.Empty))
                {
                    dataColumn = new DataColumn();
                    dataColumn.DataType = Type.GetType("System.String");
                    dataColumn.ColumnName = range.Text.ToString().Trim();
                    dataTable.Columns.Add(dataColumn);

                    range = (Range)excelWorksheet.Cells[1, ++ColumnID];
                }

                for (int row = 2; row <= rowCount; row++)
                {
                    DataRow dataRow = dataTable.NewRow();

                    for (int col = 1; col <= colCount; col++)
                    {
                        range = (Range)excelWorksheet.Cells[row, col];

                        cellContent = (range.Value2 == null) ? string.Empty : range.Text.ToString();

                        // if (iRow == 1)  
                        // {  
                        // dt.Columns.Add(cellContent);  
                        // }  
                        // else  
                        // {  
                        dataRow[col - 1] = cellContent;

                        // }  
                    }

                    // if (iRow != 1)  
                    dataTable.Rows.Add(dataRow);
                }

                return dataTable;
            }
            catch
            {
                return dataTable;
            }
            finally
            {
                excelWorkwbook.Close(false);
                Marshal.ReleaseComObject(excelWorkwbook);
                excelWorkwbook = null;
                excelApp.Workbooks.Close();
                excelApp.Quit();
                Marshal.ReleaseComObject(excelApp);
                excelApp = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        /// <summary>
        /// The get xml data.
        /// </summary>
        /// <param name="xmlFilePath">
        /// The xml file path.
        /// </param>
        /// <param name="tableName">
        /// The table Name.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable"/>.
        /// </returns>
        public DataTable GetXmlData(string xmlFilePath, string tableName)
        {
            DataSet dataSet = this.GetXmlDataSet(xmlFilePath);

            DataTable dataTable = dataSet.Tables[tableName];

            return dataTable;
        }

        /// <summary>
        /// The get xml data.
        /// </summary>
        /// <param name="xmlFilePath">
        /// The xml file path.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable"/>.
        /// </returns>
        public DataTable GetXmlData(string xmlFilePath)
        {
            DataSet dataSet = this.GetXmlDataSet(xmlFilePath);

            DataTable dataTable = dataSet.Tables[0];

            return dataTable;
        }

        /// <summary>
        /// The get xml data set.
        /// </summary>
        /// <param name="xmlFilePath">
        /// The xml file path.
        /// </param>
        /// <returns>
        /// The <see cref="DataSet"/>.
        /// </returns>
        public DataSet GetXmlDataSet(string xmlFilePath)
        {
            var xmlDoc = new XmlDocument();

            xmlDoc.LoadXml(xmlFilePath);

            DataSet dataSet;
            using (XmlReader xmlReader = XmlReader.Create(new StringReader(xmlDoc.OuterXml)))
            {
                dataSet = new DataSet();

                dataSet.ReadXml(xmlReader);
            }

            return dataSet;
        }

        /// <summary>
        /// The update excel data.
        /// </summary>
        /// <param name="excelFilePath">
        /// The excel file path.
        /// </param>
        /// <param name="worksheetName">
        /// The worksheet name.
        /// </param>
        /// <param name="editRow">
        /// The edit row.
        /// </param>
        /// <param name="editColumn">
        /// The edit column.
        /// </param>
        /// <param name="editValue">
        /// The edit value.
        /// </param>
        /// <exception cref="Exception">
        /// </exception>
        public void UpdateExcelData(
            string excelFilePath, 
            string worksheetName, 
            int editRow, 
            int editColumn, 
            string editValue)
        {
            var excelApp = new Application();
            Sheets excelSheets;
            Workbook excelWorkwbook = null;

            try
            {
                excelWorkwbook = excelApp.Workbooks.Open(excelFilePath);
                excelSheets = excelWorkwbook.Worksheets;
                var excelWorksheet = (Worksheet)excelSheets.Item[worksheetName];

                if (excelWorksheet == null)
                {
                    throw new Exception("Excel Sheet is null");
                }

                excelWorksheet.Cells[editRow, editColumn] = editValue;
            }
            catch (Exception)
            {
            }
            finally
            {
                excelWorkwbook.Close(true);
                Marshal.ReleaseComObject(excelWorkwbook);
                excelWorkwbook = null;
                excelApp.Workbooks.Close();
                excelApp.Quit();
                Marshal.ReleaseComObject(excelApp);
                excelApp = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        #endregion
    }
}